package it.sandler.android.database;

import it.sandler.android.database.types.SandlerBool;
import it.sandler.android.database.types.SandlerFloat;
import it.sandler.android.database.types.SandlerInteger;
import it.sandler.android.database.types.SandlerLong;
import it.sandler.android.database.types.SandlerFieldRelation;
import it.sandler.android.database.types.SandlerText;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map.Entry;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * You can do what ever you want to do with SandlerDB.<br>
 * There are no restrictions. You can use SandlerDB for commercial use.<br>
 * <br>
 * Circle me on Google+: http://gplus.to/roberthahn<br>
 * SandlerDB on Google Code: http://code.google.com/p/sandler-db-for-android/<br>
 * <br>
 * Date: 04/16/2012
 * 
 * @author Robert Hahn
 * 
 */
public class SandlerDatabase extends SQLiteOpenHelper {

	public enum Recursion {
		NONE, FULL, FIRST_CHILDREN
	}

	private final Class<? extends SandlerEntity>[] entities;
	private final int databaseVersion;
	private final String databaseName;
	private SQLiteDatabase database;
	private final ArrayList<SandlerBaseTable> tables = new ArrayList<SandlerBaseTable>();

	/**
	 * Yeah I know, calling this constructor raises a warning. In my opinion
	 * this is a bug of Java.<br>
	 * There are a lot of threads on Stackoverflow about this issue.<br>
	 * Add <code>@SuppressWarnings("unchecked")</code> and you're done ;) <br>
	 * <i>Example:</i> <br>
	 * <code>db = new SandlerDatabase(this, "tasks", 4, TaskList.class, Task.class); </code>
	 * <br>
	 * TaskList and Task are extending {@link SandlerEntity}
	 * 
	 * @see {@link SandlerEntity}
	 * 
	 * @param context
	 * @param databaseName
	 * @param databaseVersion
	 * @param entities
	 */
	public SandlerDatabase(Context context, String databaseName,
			int databaseVersion, Class<? extends SandlerEntity>... entities) {
		super(context, databaseName, null, databaseVersion);

		// assign values
		this.databaseName = databaseName;
		this.databaseVersion = databaseVersion;
		this.entities = entities;
		SandlerTable[] stables = new SandlerTable[entities.length];

		// link entities with tables
		for (int i = 0; i < entities.length; i++) {
			stables[i] = new SandlerTable(entities[i]);
		}

		for (SandlerTable tbl : stables) {
			tables.add(tbl);
			if (tbl.hasRelation()) {
				for (SandlerRelationTable rel : tbl.getRelations()) {
					boolean foundTable = false;
					for (int i = 0; i < tables.size(); i++) {
						if (tables.get(i).getTableName()
								.equals(rel.getTableName())) {
							foundTable = true;
							break;
						}
					}
					if (!foundTable)
						tables.add(rel);
				}
			}
		}

		// create or open the database
		this.database = getWritableDatabase();
	}

	private HashMap<String, SandlerEntity> recursivFindStack = new HashMap<String, SandlerEntity>();
	private HashMap<String, SandlerEntity> recursivSaveStack = new HashMap<String, SandlerEntity>();
	private Recursion recursion = Recursion.FULL;

	public void setRecursion(Recursion recursion) {
		this.recursion = recursion;
	}

	/**
	 * Saves a given item and all its children found to the database.<br>
	 * If you don't want to save all it's children and the children's children
	 * and so on, you can do this by calling
	 * {@link SandlerDatabase#setRecursion()}.<br>
	 * <br>
	 * <i>Example:</i><br>
	 * <code>Task task = new Task("Got sth to do!");</code><br>
	 * <code>db.save(task);</code>
	 * 
	 * @param item
	 * @return Returns the id of the saved item.
	 */
	public <T extends SandlerEntity, E extends SandlerEntity> long save(T item) {
		recursivSaveStack.clear();
		recursivFindStack.clear();
		return save(item, true);
	}

	@SuppressWarnings("unchecked")
	private <T extends SandlerEntity, E extends SandlerEntity> long save(
			T item, boolean overload) {
		if (!item.changed()) {
			return item.getId();
		}
		Class<T> classOf = ((Class<T>) item.getClass());
		if (item.getId() == 0) {
			// new item => insert
			ContentValues values = new ContentValues();
			values.put("___dummy___", 1);
			item.setId(database.insert(classOf.getSimpleName(), null, values));
		}
		long id = item.getId();
		for (Entry<String, SandlerEntity> k : recursivSaveStack.entrySet())
			if (k.getKey().equals(classOf.getSimpleName() + "_" + id))
				return id;
		recursivSaveStack.put(classOf.getSimpleName() + "_" + id, item);

		ContentValues values = new ContentValues();
		for (Field f : classOf.getDeclaredFields()) {
			f.setAccessible(true);

			if (SandlerField.isSandlerType(f)) {
				// its a normal field
				try {
					String type = f.getType().getSimpleName();
					String name = f.getName();

					if (type.equals(SandlerBool.class.getSimpleName())) {
						SandlerBool sf = ((SandlerBool) f.get(item));
						if (sf == null)
							sf = SandlerBool.class.newInstance();
						values.put(name, sf.get());
					} else if (type
							.equals(SandlerInteger.class.getSimpleName())) {
						SandlerInteger sf = ((SandlerInteger) f.get(item));
						if (sf == null)
							sf = SandlerInteger.class.newInstance();
						values.put(name, sf.get());
					} else if (type.equals(SandlerText.class.getSimpleName())) {
						SandlerText sf = ((SandlerText) f.get(item));
						if (sf == null)
							sf = SandlerText.class.newInstance();
						values.put(name, sf.get());
					} else if (type.equals(SandlerLong.class.getSimpleName())) {
						SandlerLong sf = ((SandlerLong) f.get(item));
						if (sf == null)
							sf = SandlerLong.class.newInstance();
						values.put(name, sf.get());
					} else if (type.equals(SandlerFloat.class.getSimpleName())) {
						SandlerFloat sf = ((SandlerFloat) f.get(item));
						if (sf == null)
							sf = SandlerFloat.class.newInstance();
						values.put(name, sf.get());
					}
				} catch (Exception e) {
					// shouldnt happen...
					e.printStackTrace();
				}
			} else if (SandlerField.isSandlerEntity(f)
					&& (recursion == Recursion.FULL || recursion == Recursion.FIRST_CHILDREN
							&& overload)) {
				// its a relation; save the other entity first and link its id
				try {
					E relation = (E) ((SandlerFieldRelation<E>) f.get(item))
							.get();
					long relatedId = 0;
					if (relation != null)
						relatedId = save(relation, false);
					values.put(SandlerField.getFieldName(f), relatedId);
				} catch (Exception e) {
					// shouldnt happen...
					e.printStackTrace();
				}
			} else if (SandlerField.isList(f)
					&& (recursion == Recursion.FULL || recursion == Recursion.FIRST_CHILDREN
							&& overload)) {
				try {
					ParameterizedType listType = (ParameterizedType) f
							.getGenericType();
					Class<?> listGenericClass = (Class<?>) listType
							.getActualTypeArguments()[0];

					SandlerList<SandlerEntity> lst = ((SandlerList<SandlerEntity>) f
							.get(item));
					SandlerRelationTable rel = new SandlerRelationTable(
							classOf, listGenericClass);

					// delete old entries
					getDatabase().delete(
							rel.getTableName(),
							"\"" + rel.getFieldName(classOf) + "\"='" + id
									+ "'", null);
					// first link the objects which already exists in the DB
					getDatabase().beginTransaction();
					if (lst != null)
						for (SandlerEntity t : lst.get()) {
							if (t != null && t.getId() > 0) {
								ContentValues vals = new ContentValues();
								vals.put(rel.getFieldName(t.getClass()),
										t.getId());
								vals.put(rel.getFieldName(classOf), id);
								getDatabase().insert(rel.getTableName(), null,
										vals);
								getDatabase().insert(rel.getTableName(), null,
										vals);
							}
						}
					getDatabase().setTransactionSuccessful();
					getDatabase().endTransaction();

					// link the newly added ones
					if (lst != null)
						for (SandlerEntity t : lst.get()) {
							if (t != null && t.getId() == 0) {
								long save = save(t, false);
								ContentValues vals = new ContentValues();
								vals.put(rel.getFieldName(t.getClass()), save);
								vals.put(rel.getFieldName(classOf), id);
								getDatabase().insert(rel.getTableName(), null,
										vals);
							}
						}
				} catch (Exception e) {
					// shouldnt happen...
					e.printStackTrace();
				}
			}

			f.setAccessible(false);
		}
		values.put("___dummy___", 0);
		database.update(classOf.getSimpleName(), values, "\"___id___\"='"
				+ item.getId() + "'", null);
		item.setChanged(false);
		return item.getId();
	}

	private <T extends SandlerEntity> Class<? extends SandlerEntity> getClass(
			String name) {
		for (Class<? extends SandlerEntity> e : entities) {
			if (e.getSimpleName().equals(name))
				return e;
		}
		return null;
	}

	/**
	 * You may need this one for some testing reasons.
	 */
	public void drop() {
		// drop old database
		getDatabase().beginTransaction();
		for (SandlerBaseTable tbl : tables) {
			getDatabase().execSQL(tbl.drop());
		}
		getDatabase().setTransactionSuccessful();
		getDatabase().endTransaction();

		// create new tables
		onCreate(getDatabase());
	}

	/**
	 * Searchs an {@link SandlerEntity} in the Database.<br>
	 * The Entity and all it's children and the children's children and so on
	 * will be fetched too.<br>
	 * You can control the recursion for fetching and saving data by calling
	 * {@link SandlerDatabase#setRecursion()}<br>
	 * <br>
	 * <i>Example:</i><br>
	 * <code>db.find(Task.class, 20);</code>
	 * 
	 * @param entityClass
	 * @param id
	 * @return
	 */
	public <T extends SandlerEntity, E extends SandlerEntity> T find(
			Class<T> entityClass, long id) {
		recursivSaveStack.clear();
		recursivFindStack.clear();
		T find = find(entityClass, id, true);
		return find;
	}

	/**
	 * Searchs for an Relation between two entities given by their classes.<br>
	 * The firstEntity will be used as condition.<br>
	 * <i>Example:</i><br>
	 * <code>find(Task.class, TaskList.class, 42);</code><br>
	 * This will search the relation between Task and TaskList where the task_id
	 * = 42<br>
	 * By swapping the two entities you can swap the condition.<br>
	 * You can get the first entity result by calling
	 * {@link SandlerRelation#getFirstItem()} and the second one by calling
	 * {@link SandlerRelation#getSecondItem()}.<br>
	 * {@link SandlerRelation#get(Class)} gives you the posibility to get the
	 * item related to the given entity.<br>
	 * If both entities are from the same type the first one will be returned
	 * when calling get(Class).
	 * 
	 * @param firstEntity
	 * @param secondEntity
	 * @param id
	 * @return null if the constructed Relation doesn't exist, no entry was
	 *         found or an entrie was found but the related entities couldn't be
	 *         found.
	 */
	public <T extends SandlerEntity, E extends SandlerEntity> SandlerRelation<T, E> find(
			Class<T> firstEntity, Class<E> secondEntity, long id) {
		recursivSaveStack.clear();
		recursivFindStack.clear();
		SandlerRelationTable sandlerRelationTable = new SandlerRelationTable(
				firstEntity, secondEntity);
		boolean found = false;
		for (SandlerBaseTable tbl : tables) {
			if (tbl.getTableName().equals(sandlerRelationTable.getTableName())) {
				found = true;
				break;
			}
		}
		if (!found)
			return null;

		Cursor query = getDatabase().query(
				sandlerRelationTable.getTableName(),
				null,
				"\"" + sandlerRelationTable.getFieldName(firstEntity) + "\"='"
						+ id + "'", null, null, null, null);
		if (query.getCount() == 0)
			return null;
		query.moveToFirst();

		T item = find(firstEntity,
				query.getInt(query.getColumnIndex(sandlerRelationTable
						.getFieldName(firstEntity))));
		E item1 = find(secondEntity,
				query.getInt(query.getColumnIndex(sandlerRelationTable
						.getFieldName(secondEntity))));

		SandlerRelation<T, E> sandlerRelation = new SandlerRelation<T, E>(item,
				item1);
		return sandlerRelation;
	}

	// TODO findAll for SandlerRelationTable

	/**
	 * Searchs all {@link SandlerEntity} in the Database.<br>
	 * The Entity and all it's children and the children's children and so on
	 * will be fetched too.<br>
	 * You can control the recursion for fetching and saving data by calling
	 * {@link SandlerDatabase#setRecursion()}<br>
	 * <br>
	 * <i>Example:</i><br>
	 * <code>db.findAll(Task.class, "'priority' >= '3'");</code>
	 * 
	 * @param entityClass
	 * @param condition
	 * @return
	 */
	public <T extends SandlerEntity, E extends SandlerEntity> ArrayList<T> findAll(
			Class<T> classOf, String condition) {
		ArrayList<T> result = new ArrayList<T>();
		Cursor cursor = getDatabase().query(classOf.getSimpleName(), null,
				condition, null, null, null, null);
		if (cursor.getCount() == 0)
			return result;
		cursor.moveToFirst();
		do {
			long id = cursor.getLong(cursor.getColumnIndex("___id___"));
			try {
				T r = classOf.newInstance();
				r.setId(id);
				r = find(classOf, r, cursor, true);
				if (!result.contains(r))
					result.add(r);
			} catch (Exception e) {
				e.printStackTrace();
			}

		} while (cursor.moveToNext());

		return result;
	}

	/**
	 * Searchs all {@link SandlerEntity} in the Database.<br>
	 * The Entity and all it's children and the children's children and so on
	 * will be fetched too.<br>
	 * You can control the recursion for fetching and saving data by calling
	 * {@link SandlerDatabase#setRecursion()}<br>
	 * <br>
	 * <i>Example:</i><br>
	 * <code>db.findAll(Task.class);</code>
	 * 
	 * @param entityClass
	 * @return
	 */
	public <T extends SandlerEntity, E extends SandlerEntity> ArrayList<T> findAll(
			Class<T> classOf) {
		return findAll(classOf, null);
	}

	@SuppressWarnings("unchecked")
	private <T extends SandlerEntity, E extends SandlerEntity> T find(
			Class<T> classOf, T result, Cursor cursor, boolean overload) {
		try {
			recursivFindStack.put(
					classOf.getSimpleName() + "_" + result.getId(), result);
			for (int i = 0; i < cursor.getColumnCount(); i++) {
				if (cursor.getColumnName(i).equals("___dummy___")) {
					// ignore
				} else if (cursor.getColumnName(i).equals("___id___")) {
					// set id
					result.setId(cursor.getLong(i));
				} else if (cursor.getColumnName(i).indexOf("___rel___") >= 0
						&& (recursion == Recursion.FULL || recursion == Recursion.FIRST_CHILDREN
								&& overload)) {
					// its a relation
					String col = cursor.getColumnName(i);
					String fieldName = col.substring(0,
							col.indexOf("___rel___"));
					String className = col.substring(col.indexOf("___rel___")
							+ "___rel___".length());
					Class<? extends SandlerEntity> relatedClass = getClass(className);
					E e = (E) find(relatedClass, cursor.getInt(i), false);

					// apply entity to field
					for (Field f : classOf.getDeclaredFields()) {
						if (f.getName().equals(fieldName)) {
							f.setAccessible(true);
							f.set(result, new SandlerFieldRelation<E>(e));
							f.setAccessible(false);
							break;
						}
					}
				} else {
					// normal field
					for (Field f : classOf.getDeclaredFields()) {
						if (f.getName().equals(cursor.getColumnName(i))) {
							f.setAccessible(true);
							String type = f.getType().getSimpleName();
							if (type.equals(SandlerBool.class.getSimpleName()))
								f.set(result, SandlerType.create(
										SandlerBool.class,
										cursor.getShort(i) == 1));
							else if (type.equals(SandlerInteger.class
									.getSimpleName()))
								f.set(result, SandlerType.create(
										SandlerInteger.class, cursor.getInt(i)));
							else if (type.equals(SandlerText.class
									.getSimpleName()))
								f.set(result, SandlerType.create(
										SandlerText.class, cursor.getString(i)));
							else if (type.equals(SandlerLong.class
									.getSimpleName()))
								f.set(result, SandlerType.create(
										SandlerLong.class, cursor.getLong(i)));
							else if (type.equals(SandlerFloat.class
									.getSimpleName()))
								f.set(result, SandlerType.create(
										SandlerFloat.class, cursor.getFloat(i)));

							f.setAccessible(false);
							break;
						}
					}
				}
			}
			// lists
			if (recursion == Recursion.FULL
					|| recursion == Recursion.FIRST_CHILDREN && overload)
				for (Field f : classOf.getDeclaredFields()) {
					if (SandlerField.isList(f)) {
						f.setAccessible(true);
						ParameterizedType listType = (ParameterizedType) f
								.getGenericType();
						Class<? extends SandlerEntity> listGenericClass = (Class<? extends SandlerEntity>) listType
								.getActualTypeArguments()[0];

						SandlerList<SandlerEntity> lst = new SandlerList<SandlerEntity>(
								result);
						SandlerRelationTable rel = new SandlerRelationTable(
								classOf, listGenericClass);
						Cursor query = getDatabase().query(
								rel.getTableName(),
								null,
								"\"" + rel.getFieldName(classOf) + "\"='"
										+ result.getId() + "'", null, null,
								null, null);
						if (query.getCount() > 0) {
							query.moveToFirst();
							do {
								int fetchId = query
										.getInt(query.getColumnIndex(rel
												.getFieldName(listGenericClass)));
								SandlerEntity find = find(listGenericClass,
										fetchId, false);
								if (find != null) {
									lst.add(find);
								}
							} while (query.moveToNext());
						}
						f.set(result, lst);
						f.setAccessible(false);
						break;
					}
				}
		} catch (Exception e) {
			e.printStackTrace();
		}
		result.setChanged(false);
		return result;
	}

	@SuppressWarnings("unchecked")
	private <T extends SandlerEntity, E extends SandlerEntity> T find(
			Class<T> classOf, long id, boolean overload) {
		for (Entry<String, SandlerEntity> k : recursivFindStack.entrySet())
			if (k.getKey().equals(classOf.getSimpleName() + "_" + id))
				return (T) k.getValue();
		T result = null;

		Cursor cursor = getDatabase().query(classOf.getSimpleName(), null,
				"\"___id___\"='" + id + "'", null, null, null, null);
		if (cursor.getCount() == 0)
			return result;
		cursor.moveToFirst();
		try {
			result = classOf.newInstance();
			result.setId(id);
			return find(classOf, result, cursor, overload);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void onCreate(SQLiteDatabase arg0) {
		// create new tables
		arg0.beginTransaction();
		for (SandlerBaseTable tbl : tables) {
			arg0.execSQL(tbl.create());
		}
		arg0.setTransactionSuccessful();
		arg0.endTransaction();
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// drop old database
		arg0.beginTransaction();
		for (SandlerBaseTable tbl : tables) {
			arg0.execSQL(tbl.drop());
		}
		arg0.setTransactionSuccessful();
		arg0.endTransaction();

		// create new tables
		onCreate(arg0);
	}

	/**
	 * Sometimes you need access to the raw Database...
	 * 
	 * @return
	 */
	public SQLiteDatabase getDatabase() {
		return database;
	}

	public int getVersion() {
		return databaseVersion;
	}

	public String getName() {
		return databaseName;
	}

}
